﻿using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using WebAssistant.BusinessLogic.Model;
using WebAssistant.Common;

namespace WebAssistant.BusinessLogic
{
    public class DataAccess
    {
        #region "数据库"

        /// <summary>
        /// 数据库文件路径
        /// </summary>
        static string DB_PATH = AppDomain.CurrentDomain.BaseDirectory + "assistant.db";


        /// <summary>
        /// 检查数据库文件是否存在
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        private static bool CheckFileExists(string fileName)
        {
            try
            {
                if (Directory.Exists(fileName))
                    return true;
            }
            catch { }
            return false;
        }

        public static SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
        {
            DbType = DbType.Sqlite,
            ConnectionString = DBConnection.ConnectionString,
            InitKeyType = InitKeyType.Attribute,
            IsAutoCloseConnection = true
        });

        /// <summary>
        /// 初始化数据库 
        /// </summary>
        public static void InitializeDatabase()
        {
            db.DbMaintenance.CreateDatabase();

            //创建表"Languages"和"LanguageResources" 以及新建初始记录
            InitLanguages();


            //创建表"Settings" 和新建初始记录
            InitSettings();

            //创建表"Interfaces" 和新建初始记录
            InitInterfaces();

            //创建表"Printers"
            InitPrinters();

            //创建表"Logs" 
            InitLogs();

            //创建表"Reports"
            InitReports();
        }

        #endregion

        #region "Odoo语言"
        private static void InitLanguages()
        {
            db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Languages));
            db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(LanguageResources));

            //if (!CheckLanguagesExists("en_US"))
            //{
            //    var data = new Languages { Name = "English (US)", Code = "en_US", Icon = "en_US", IsDeleted = false };
            //    var count = db.Insertable(data).ExecuteCommand();
            //}
            //if (!CheckLanguagesExists("zh_CN"))
            //{
            //    var data = new Languages { Name = "Chinese (Simplified) / 简体中文", Code = "zh_CN", Icon = "zh_CN", IsDeleted = false };
            //    var count = db.Insertable(data).ExecuteCommand();
            //}
        }

        public static bool CheckLanguagesExists(string code)
        {
            try
            {
                if (db.Queryable<Languages>().Where(Language => Language.Code == code).Count() == 0)
                {
                    return false; ;
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        public static int InsertLanguage(Languages languages)
        {
            var result = db.Insertable(languages).ExecuteCommand();
            return result;
        }

        public static List<Languages> GetActivatedLanguages()
        {
            List<Languages> list = db.Queryable<Languages>().Where(languages => languages.Active == true).ToList();
            return list;
        }

        #endregion

        #region "Odoo国家"

        private static void InitCountries()
        {
            db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Countries));
        }

        public static Countries GetCountryByCountryId(int id)
        {
            return db.Queryable<Countries>().First(country => country.CountryId == id);
        }

        public static bool CheckCountryExists(int id)
        {
            try
            {
                if (db.Queryable<Countries>().Where(Country => Country.CountryId == id).Count() == 0)
                {
                    return false; ;
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        public static int InsertCountry(Countries country)
        {
            var result = db.Insertable(country).ExecuteCommand();
            return result;
        }

        public static int InsertCountryList(List<Countries> countryList)
        {
            var result = db.Insertable(countryList).ExecuteCommand();
            return result;
        }

        public static int UpdateCountry(Countries country, int id)
        {
            var result = db.Updateable(country)
                .Where(cou => cou.CountryId == id)
                .ExecuteCommand();
            return result;
        }

        public static int UpdateCountryList(List<Countries> countryList)
        {
            var result = db.Updateable(countryList).ExecuteCommand();
            return result;
        }

        #endregion

        #region "Odoo 国家/地区省/州"

        private static void InitCountryStates() {
            db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(CountryStates));
        }

        public static CountryStates GetStateByStateId(int id)
        {
            return db.Queryable<CountryStates>().First(state => state.StateId == id);
        }

        public static bool CheckCountryStateExists(int id)
        {
            try
            {
                if (db.Queryable<CountryStates>().Where(State => State.StateId == id).Count() == 0)
                {
                    return false; 
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }


        public static int InsertCountryState(CountryStates state)
        {
            var result = db.Insertable(state).ExecuteCommand();
            return result;
        }

        public static int InsertCountryStateList(List<CountryStates>  stateList)
        {
            var result = db.Insertable(stateList).ExecuteCommand();
            return result;
        }

        public static int UpdateCountryState(CountryStates state, int id)
        {
            var result = db.Updateable(state)
                .Where(cs => cs.StateId == id)
                .ExecuteCommand();
            return result;
        }

        public static int UpdateCountryStateList(List<CountryStates> stateList)
        {
            var result = db.Updateable(stateList).ExecuteCommand();
            return result;
        }

        #endregion

        #region "Odoo公司"

        private static void InitCompanies()
        {
            db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Companies));
        }

        public static bool CheckCompanyExists(int id)
        {
            try
            {
                if (db.Queryable<Companies>().Where(Company => Company.CompanyId == id).Count() == 0)
                {
                    return false; ;
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        public static Companies GetCompanyByCompanyId(int id)
        {
            return db.Queryable<Companies>().First(company => company.CompanyId == id);
        }

        public static int InsertCompany(Companies company)
        {
            var result = db.Insertable(company).ExecuteCommand();
            return result;
        }

        public static int UpdateCompany(Companies company,int id)
        {
            var result = db.Updateable(company)
                .Where(com => com.CompanyId == id)
                .ExecuteCommand();
            return result;
        }

        public static string GetCompanyLogoBase64String(int id)
        {
            var company = db.Queryable<Companies>().First(company => company.Id == id);
            return company.Logo;
        }

        #endregion

        #region "Odoo联系人"
        private static void InitPartners()
        {
            db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Partners));
        }

        public static Partners GetPartnerByPartnerId(int id)
        {
            return db.Queryable<Partners>().First(partner => partner.PartnerId == id);
        }

        public static bool CheckPartnerExists(int id)
        {
            try
            {
                if (db.Queryable<Partners>().Where(partner => partner.PartnerId == id).Count() == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        public static int InsertPartnerList(List<Partners> partnerList)
        {
            var result = db.Insertable(partnerList).ExecuteCommand();
            return result;
        }

        public static int UpdatePartnerList(List<Partners> partnerList)
        {
            var result = db.Updateable(partnerList).ExecuteCommand();
            return result;
        }

        #endregion

        #region "Odoo系统用户"
        private static void InitUsers()
        {
            db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Users));
        }

        public static Users GetUserByUserId(int id)
        {
            return db.Queryable<Users>().First(user => user.UserId == id);
        }

        public static bool CheckUserExists(int id)
        {
            try
            {
                if (db.Queryable<Users>().Where(user => user.UserId == id).Count() == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        public static int InsertUserList(List<Users> userList)
        {
            var result = db.Insertable(userList).ExecuteCommand();
            return result;
        }

        public static int UpdateUserList(List<Users> userList)
        {
            var result = db.Updateable(userList).ExecuteCommand();
            return result;
        }

        #endregion

        #region "设置"

        private static void InitSettings()
        {
            db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Settings));
            if (!CheckSettingExists("assistant.port"))
            {
                var data = new Settings { Key = "assistant.port", Value = "18069" };
                var count = db.Insertable(data).ExecuteCommand();
            }
            if (!CheckSettingExists("assistant.lock_screen_status"))
            {
                var data = new Settings { Key = "assistant.lock_screen_status", Value = "false" };
                var count = db.Insertable(data).ExecuteCommand();
            }
            if (!CheckSettingExists("assistant.password"))
            {
                var data = new Settings { Key = "assistant.password", Value = "" };
                var count = db.Insertable(data).ExecuteCommand();
            }
            if (!CheckSettingExists("assistant.log_retention_days"))
            {
                var data = new Settings { Key = "assistant.log_retention_days", Value = "30" };
                var count = db.Insertable(data).ExecuteCommand();
            }
            if (!CheckSettingExists("assistant.auto_minimize"))
            {
                var data = new Settings { Key = "assistant.auto_minimize", Value = "false" };
                var count = db.Insertable(data).ExecuteCommand();
            }
            if (!CheckSettingExists("assistant.default_printer"))
            {
                var data = new Settings { Key = "assistant.default_printer", Value = "" };
                var count = db.Insertable(data).ExecuteCommand();
            }
            if (!CheckSettingExists("assistant.interfaces_panel_collapsed"))
            {
                var data = new Settings { Key = "assistant.interfaces_panel_collapsed", Value = "false" };
                var count = db.Insertable(data).ExecuteCommand();
            }
            if (!CheckSettingExists("assistant.style"))
            {
                var data = new Settings { Key = "assistant.style", Value = "Blue" };
                var count = db.Insertable(data).ExecuteCommand();
            }
            if (!CheckSettingExists("odoo.key"))
            {
                var data = new Settings { Key = "odoo.key", Value = SecurityHelper.GetRandomPassword(24) };
                var count = db.Insertable(data).ExecuteCommand();
            }
            if (!CheckSettingExists("odoo.server.url"))
            {
                var data = new Settings { Key = "odoo.server.url", Value = "" };
                var count = db.Insertable(data).ExecuteCommand();
            }
            if (!CheckSettingExists("odoo.server.database"))
            {
                var data = new Settings { Key = "odoo.server.database", Value = "" };
                var count = db.Insertable(data).ExecuteCommand();
            }
            if (!CheckSettingExists("odoo.server.user"))
            {
                var data = new Settings { Key = "odoo.server.user", Value = "" };
                var count = db.Insertable(data).ExecuteCommand();
            }
            if (!CheckSettingExists("odoo.server.password"))
            {
                var data = new Settings { Key = "odoo.server.password", Value = "" };
                var count = db.Insertable(data).ExecuteCommand();
            }
        }

        /// <summary>
        /// 检测设置是否存在
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static bool CheckSettingExists(string key)
        {
            try
            {
                if (db.Queryable<Settings>().Where(Setting => Setting.Key == key).Count() == 0)
                {
                    return false; ;
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 通过KEY查询设置值
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static string GetSettingByKey(string key)
        {
            return db.Queryable<Settings>().First(setting => setting.Key == key).Value.ToString();
        }

        /// <summary>
        /// 通过KEY更新设置值
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static int UpdateSettingByKey(string key, string value)
        {
            var result = db.Updateable<Settings>(setting => setting.Value == value).Where(setting => setting.Key == key).ExecuteCommand();

            return result;
        }

        /// <summary>
        /// 批量更新或插入
        /// </summary>
        /// <param name="settings"></param>
        /// <returns></returns>
        public static bool BatchUpdateInsertSetting(List<Settings> settings)
        {
            try
            {
                foreach (Settings setting in settings)
                {
                    if (db.Queryable<Settings>().Where(s => s.Key == setting.Key).Any())
                    {
                        //存在
                        db.Updateable(setting).ExecuteCommand();
                    }
                    else
                    {
                        //不存在
                        db.Insertable(setting).ExecuteCommand();
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region "接口"
        private static void InitInterfaces()
        {
            db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Interfaces));

        }

        public static bool GetInterfaceIsInstalled(string key)
        {
            try
            {
                if (db.Queryable<Interfaces>().Where(Interface => Interface.Key == key).Count() == 0)
                {
                    return false; ;
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                return false;

            }
        }

        /// <summary>
        /// 安装服务
        /// </summary>
        /// <param name="interfaces"></param>
        /// <returns></returns>
        public static int InstallInterface(Interfaces interfaces)
        {
            var result = db.Insertable(interfaces).ExecuteCommand();
            return result;
        }

        public static int UninstallInterfaceByKey(string key)
        {
            var result =  db.Deleteable<Interfaces>().Where(interfaces => interfaces.Key == key).ExecuteCommand();
            return result;
        }

        /// <summary>
        /// 获取全部接口列表
        /// </summary>
        /// <returns></returns>
        public static List<Interfaces> GetInterfaceList()
        {
            List<Interfaces> list = db.Queryable<Interfaces>().ToList();
            return list;
        }

        /// <summary>
        /// 获取运行中的接口
        /// </summary>
        /// <returns></returns>
        public static List<Interfaces> GetRunningInterfaceList()
        {
            List<Interfaces> list = db.Queryable<Interfaces>().Where(interfaces => interfaces.State == true).ToList();
            return list;
        }


        public static Interfaces GetInterfaceByKey(string key)
        {
            Interfaces interfaces = db.Queryable<Interfaces>().First(interfaces => interfaces.Key == key && interfaces.Category ==0);
            return interfaces;
        }

        /// <summary>
        /// 通过KEY获取运行中的打印服务
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static bool GetRunPrintServerByKey(string key)
        {
            var count = db.Queryable<Interfaces>().Where(interfaces => interfaces.Key == key && interfaces.State ==true && interfaces.Category == 0).Count();
            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据KEY更新接口服务的自动启动 
        /// </summary>
        /// <param name="interfaces"></param>
        /// <returns></returns>
        public static int UpdateInterfaceAutoStartByKey(string key,bool auto)
        {
            Interfaces interfaces = db.Queryable<Interfaces>().First(interfaces => interfaces.Key == key);
            interfaces.AutoStart = auto;
            var count = db.Updateable(interfaces).UpdateColumns(interfaces => new { interfaces.AutoStart }).ExecuteCommand();
            return count;
        }

        public static int UpdateInterfaceStateByKey(string key,bool state)
        {
            Interfaces interfaces = db.Queryable<Interfaces>().First(interfaces => interfaces.Key == key);
            interfaces.State = state;
            var count = db.Updateable(interfaces).UpdateColumns(interfaces => new { interfaces.State }).ExecuteCommand();
            return count;
        }



        #endregion

        #region "打印机"

        private static void InitPrinters()
        {
            db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Printers));
        }

        /// <summary>
        /// 根据Key判断打印机配置是否存在
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static bool CheckPrinterExists(string key)
        {
            try
            {
                if (db.Queryable<Printers>().Where(Setting => Setting.Key == key).Count() == 0)
                {
                    return false; ;
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 根据KEY查询打印机属性
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static Printers GetPrinterOptionsByKey(string key)
        {
            Printers printers = db.Queryable<Printers>().First(printers => printers.Key == key);
            return printers;
        }

        /// <summary>
        /// 插入打印机
        /// </summary>
        /// <param name="printers"></param>
        /// <returns></returns>
        public static int InsertPrinter(Printers printers)
        {
            var result = db.Insertable(printers).ExecuteCommand();
            return result;
        }

        /// <summary>
        /// 更新打印机
        /// </summary>
        /// <param name="key"></param>
        /// <param name="printers"></param>
        /// <returns></returns>
        public static int UpdatePrinterByKey(string key, Printers printers)
        {
            var result = db.Updateable(printers).Where(printer => printer.Key == key).ExecuteCommand();
            return result;
        }
        #endregion

        #region "日志"

        private static void InitLogs()
        {
            db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Logs));
        }

        public static int InsertLogs(Logs logs)
        {
            var result = db.Insertable(logs).ExecuteCommand();
            return result;
        }

        /// <summary>
        /// 根据时间获取日志,降序排列
        /// </summary>
        /// <param name="time"></param>
        /// <returns></returns>
        public static List<Logs> GetLogsByTime(DateTime time)
        {
            List<Logs> list = db.Queryable<Logs>().Where(logs => logs.StartDate > time).OrderBy(logs => logs.StartDate, OrderByType.Desc).ToList();
            return list;
        }

        #endregion

        #region "报表"
        
        private static void InitReports()
        {
            db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Reports));
        }

        public static bool CheckReportExistsByReportName(string name)
        {
            try
            {
                if (db.Queryable<Reports>().Where(report => report.ReportName == name).Count() == 0)
                {
                    return false; 
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        public static Reports GetReportByReportName(string name)
        {
            return db.Queryable<Reports>().First(report => report.ReportName == name);
        }

        public static List<Reports> GetAllReports()
        {
            return db.Queryable<Reports>().OrderBy(r => r.ReportName, OrderByType.Asc).ToList();
        }
        
        public static Tuple<List<Reports>,int> GetReportPageData(int pageIndex, int pageSize, int totalCount)
        {
            var page = db.Queryable<Reports>().OrderBy(r => r.ReportName, OrderByType.Asc).ToPageList(pageIndex, pageSize, ref totalCount);
            return new Tuple<List<Reports>, int>(page, totalCount);
        }

        public static int InsertReport(Reports reports)
        {
            var result = db.Insertable(reports).ExecuteCommand();
            return result;
        }

        public static int InsertReport(List<Reports> reports)
        {
            var result = db.Insertable(reports).ExecuteCommand();
            
                return result;
        }


        public static int BatchUpdateInsertReport(List<Reports> reports)
        {
            int result = 0;
            try
            {
                foreach (Reports report in reports)
                {
                    bool exist = db.Queryable<Reports>().Where(r => r.ReportName == report.ReportName).Any();
                    if (exist)
                    {
                        //存在
                        db.Updateable(report).ExecuteCommand();
                        result +=1;
                    }
                    else
                    {
                        //不存在
                        db.Insertable(report).ExecuteCommand();
                        result += 1;
                    }
                }
                return result;
            }
            catch
            {                
                return result;
            }
        }

        public static int UpdateReportDataTemplate(Reports report)
        {
            var result =  db.Updateable(report).ExecuteCommand();
            return result;
        }

        #endregion
    }
}
